package com.liao.servlet;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDao {
	public void deleteById(Integer id){
		Connection connection=null;
		PreparedStatement preparedStatement=null;
		ResultSet resultSet=null;
		try {
			String driverClass="com.mysql.jdbc.Driver";
			String URL="jdbc:mysql://127.0.0.1:3306/user";
			String USER="root";
			String PASSWORD="930411";
			Class.forName(driverClass);
			connection=DriverManager.getConnection(URL,USER,PASSWORD);
			System.out.println("数据库连接成功");
			//删除
			String sql="DELETE FROM user WHERE id=?";
			preparedStatement=connection.prepareStatement(sql);
			preparedStatement.setInt(1, id);
			preparedStatement.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			
			try{
				if (preparedStatement !=null) {
					preparedStatement.close();
				}
			}catch (SQLException e) {
				e.printStackTrace();
			}
			try{
				if (connection !=null) {
					connection.close();
				}
			}catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	public List<User> getAll(){
		List<User> users=new ArrayList<>();
		Connection connection=null;
		PreparedStatement preparedStatement=null;
		ResultSet resultSet=null;
		try {
			String driverClass="com.mysql.jdbc.Driver";
			String URL="jdbc:mysql://127.0.0.1:3306/user";
			String USER="root";
			String PASSWORD="930411";
			Class.forName(driverClass);
			connection=DriverManager.getConnection(URL,USER,PASSWORD);
			System.out.println("数据库连接成功");
			//查询
			String sql="SELECT id,name,password FROM user";
			preparedStatement=connection.prepareStatement(sql);
			resultSet=preparedStatement.executeQuery();
			//System.out.println("数据库查询成功");
			while (resultSet.next()) {
				int id=resultSet.getInt(1);
				String name=resultSet.getString(2);
				String password=resultSet.getString(3);
				User user=new User(id,name,password);
				users.add(user);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try{
				if (resultSet !=null) {
					resultSet.close();
				}
			}catch (SQLException e) {
				e.printStackTrace();
			}
			try{
				if (preparedStatement !=null) {
					preparedStatement.close();
				}
			}catch (SQLException e) {
				e.printStackTrace();
			}
			try{
				if (connection !=null) {
					connection.close();
				}
			}catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return users;	
	}

}
